package org.shock.ss.sms.commons.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.struts2.ServletActionContext;
import org.shock.ss.sms.commons.db.ProxoolDbPool;

public class JspExport {
	/**
	 * ��ݴ�����ļ����sql�������ļ����������ʽ���أ����ɾ���ļ�
	 * 
	 * @param fileFileName
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public String export(String fileFileName, String sql) throws Exception {

		String path = "";
		path = getClass().getProtectionDomain().getCodeSource().getLocation().getPath();
		path = path.substring(1, path.lastIndexOf("W") - 1);
		path = path + "/system";

		HSSFWorkbook _wb = new HSSFWorkbook();
		int i = _wb.getSheetIndex(fileFileName);
		if (i >= 0)
			_wb.removeSheetAt(i);
		int result = 0;
		String dd = "";
		HSSFSheet st = _wb.createSheet(fileFileName + dd);
		HSSFRow rowTitle = st.createRow(result++);
		HSSFRow row = st.createRow(result++);
		HSSFFont font = _wb.createFont();
		HSSFCellStyle cellStyle = _wb.createCellStyle();
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		if (sql != null) {
			try {
				conn = ProxoolDbPool.getConnection();
				stmt = conn.prepareStatement(sql);
				rs = stmt.executeQuery();
				ResultSetMetaData rsmd = rs.getMetaData();
				int columnCount = rsmd.getColumnCount();
				for (int j = 1; j <= columnCount; j++) {
					HSSFCell cellTitle = rowTitle.createCell(j - 1);
					if (j == 1) {
						cellTitle.setCellValue(fileFileName);
					} else {
						cellTitle.setCellValue("");
					}
					HSSFCell cell = row.createCell((short) (j - 1));
					// cell.setEncoding(cell.ENCODING_UTF_16);
					font.setColor(HSSFFont.COLOR_RED);
					font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
					cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//
					cellStyle.setFont(font);
					cell.setCellStyle(cellStyle);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell.setCellValue(rsmd.getColumnName(j));
				}
				while (rs.next()) {
					row = st.createRow(result++);
					for (int j = 1; j <= columnCount; j++) {
						HSSFCell cell = row.createCell((short) (j - 1));
						// cell.setEncoding(cell.ENCODING_UTF_16);
						if (rsmd.getColumnTypeName(j).equals("datetime")) {
							java.sql.Date date = rs.getDate(j);
							cell.setCellValue(rs.getDate(j) == null ? null : rs.getDate(j).toString());
						} else {
							cell.setCellValue(rs.getString(j));
						}
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				ProxoolDbPool.closeResultSet(rs);
				ProxoolDbPool.closeStatement(stmt);
				ProxoolDbPool.closeConnection(conn);
			}
		}
		HttpServletRequest request = ServletActionContext.getRequest();
		FileOutputStream fileOut = new FileOutputStream(path + "/" + dd + fileFileName);
		_wb.write(fileOut);
		fileOut.close();

		return (path + "/" + dd + fileFileName);
	}

}
